package cn.edu.sanxiau.www.dao.impl;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import cn.edu.sanxiau.www.dao.RoleDao;
import cn.edu.sanxiau.www.model.Role;
import cn.edu.sanxiau.www.model.User;
import cn.edu.sanxiau.www.model.Role;
import frame.DBUtils.JdbcUtils;

public class RoleDaoImpl implements RoleDao {

    @Override
    public List<Role> queryAllRole() {
        //操作数据库
        //1.连接对象
        Connection conn = null;
        //2.预处理对象
        PreparedStatement pstmt = null;
        //3.结果集
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "SELECT * FROM role";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            //对象列表
            List<Role> roleList_db = new ArrayList<Role>();

            while (rs.next()) {
                Role role_db = new Role();
                role_db.setRole_id(rs.getInt("Role_id"));
                role_db.setName(rs.getString("name"));
                role_db.setState(rs.getString("state"));
                role_db.setDescription(rs.getString("description"));

                //放每个对象
                roleList_db.add(role_db);
            }
            return roleList_db;

        } catch (ClassNotFoundException | IOException | SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int addRoleByRole(Role role) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "INSERT INTO  role (role_id,name,state,description) VALUES(?,?,?,?)";
//	            Statement
//	            prepareStatement
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, role.getRole_id());
            pstmt.setString(2, role.getName());
            pstmt.setString(3, role.getState());
            pstmt.setString(4, role.getDescription());


            int n = pstmt.executeUpdate();
            return n;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                pstmt.close();
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return 0;
    }

    @Override
    public int deleteRoleByRole_id(int role_id) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = JdbcUtils.getConnection();

            String sql = "DELETE FROM role WHERE role_id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, role_id);

            int m = pstmt.executeUpdate();
            return m;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public Role queryRoleByRole_id(int role_id) {
        //操作数据库
        //1.连接对象
        Connection conn = null;
        //2.预处理对象
        PreparedStatement pstmt = null;
        //3.结果集
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "SELECT * FROM role WHERE role_id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, role_id);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Role role_db = new Role();
                role_db.setRole_id(rs.getInt("Role_id"));
                role_db.setName(rs.getString("name"));
                role_db.setState(rs.getString("state"));
                role_db.setDescription(rs.getString("description"));
                return role_db;

            }
        } catch (ClassNotFoundException | IOException | SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public int updateRoleByRole(Role role) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = JdbcUtils.getConnection();
            String sql = "UPDATE role SET name=?,state=?,description=? WHERE role_id=?";
            pstmt = conn.prepareStatement(sql);

            pstmt.setString(1, role.getName());
            pstmt.setString(2, role.getState());
            pstmt.setString(3, role.getDescription());
            pstmt.setInt(4, role.getRole_id());

            int n = pstmt.executeUpdate();
            return n;

        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public Role queryXZRoleByUserId(int user_id) {
        //操作数据库
        //1.连接对象
        Connection conn = null;
        //2.预处理对象
        PreparedStatement pstmt = null;
        //3.结果集
        ResultSet rs = null;
        try {
            conn = JdbcUtils.getConnection();
            String sql = "SELECT role.role_id,role.`name`,role.state,role.description FROM userrole INNER JOIN role ON userrole.role_id = role.role_id WHERE user_id=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, user_id);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                Role role_db = new Role();
                role_db.setRole_id(rs.getInt("Role_id"));
                role_db.setName(rs.getString("name"));
                role_db.setState(rs.getString("state"));
                role_db.setDescription(rs.getString("description"));
                return role_db;

            }
        } catch (ClassNotFoundException | IOException | SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

}
